﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataClassLayer
{
    public class PlayerCollection
    {
        public PlayerCollection()
        {
            
        }
        public bool Add(Player player)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Insert into SOCCER_SINGLE_PLAYER_INFO (Name,BirthDate,ClubID,NationalTeamID,Image,OfficialWebSite,PlaceOfBirth,HomeTown,Zo_diaq,GeneralInformation) Values
                   ('" + player.Name + "',Convert(datetime,'"
                   + player.BirthDate + "',103),"
                   + player.ClubID + ","
                   + player.NationalTeamID + ",'"
                   + player.Image + "','"
                   + player.OfficialWebSite + "','"
                   + player.PlaceOfBirth + "',"
                   + player.HomeTown + ",'"
                   + player.Zodiaq + "','" 
                   + player.GeneralInformation + "')";
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Edit(Player player)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Update SOCCER_SINGLE_PLAYER_INFO Set 
                     Name='" + player.Name +
                     "',BirthDate=Convert(datetime,'" + player.BirthDate +
                     "',103),ClubID=" + player.ClubID +
                     ",NationalTeamID=" + player.NationalTeamID +
                     ",Image='" + player.Image + 
                     "',OfficialWebSite='" + player.OfficialWebSite +
                     "',PlaceOfBirth='" + player.PlaceOfBirth +
                     "',HomeTown=" + player.HomeTown +
                     ",Zo_diaq='" + player.Zodiaq +
                     "',GeneralInformation='" + player.GeneralInformation +
                     "' Where ID=" + player.ID;
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Delete(Player player)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Delete From SOCCER_SINGLE_PLAYER_INFO Where ID= " + player.ID; 
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public DataTable FillList(Player player)
        {
            try
            {
                string wherestatement = WhereStatement(player);
                string command = @"SELECT        SOCCER_SINGLE_PLAYER_INFO.ID, SOCCER_SINGLE_PLAYER_INFO.Name, SOCCER_SINGLE_PLAYER_INFO.BirthDate, SOCCER_SINGLE_PLAYER_INFO.ClubID, 
                         SOCCER_CLUB_INFO.Name AS ClubName, SOCCER_SINGLE_PLAYER_INFO.NationalTeamID, SOCCER_COUNTRY.Name AS HomeTownName, 
                         SOCCER_SINGLE_PLAYER_INFO.OfficialWebSite, SOCCER_SINGLE_PLAYER_INFO.Image, SOCCER_SINGLE_PLAYER_INFO.PlaceOfBirth, 
                         SOCCER_SINGLE_PLAYER_INFO.HomeTown, SOCCER_NATIONAL_TEAM_INFO.Name AS NationalTeamName, SOCCER_SINGLE_PLAYER_INFO.Zo_diaq, 
                         SOCCER_SINGLE_PLAYER_INFO.GeneralInformation
                         FROM            SOCCER_SINGLE_PLAYER_INFO INNER JOIN
                         SOCCER_CLUB_INFO ON SOCCER_SINGLE_PLAYER_INFO.ClubID = SOCCER_CLUB_INFO.ID INNER JOIN
                         SOCCER_NATIONAL_TEAM_INFO ON SOCCER_SINGLE_PLAYER_INFO.NationalTeamID = SOCCER_NATIONAL_TEAM_INFO.ID INNER JOIN
                         SOCCER_COUNTRY ON SOCCER_SINGLE_PLAYER_INFO.HomeTown = SOCCER_COUNTRY.ID";
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    command += " Where " + wherestatement;
                }
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public int MaxID()
        {
            try
            {
                int Maxid = 1;
                string command = @"SELECT    Max(ID) AS MaxID FROM   SOCCER_SINGLE_PLAYER_INFO";
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                if (table.Rows.Count == 1)
                {
                    Maxid = int.Parse(table.Rows[0]["MaxID"].ToString());
                }
                return Maxid;
            }
            catch
            {
                return 1;
            }
        }
        public string WhereStatement(Player player)
        {
            string wherestatement = "";
            if (player.ID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_SINGLE_PLAYER_INFO.ID= " + player.ID;
                }
                else
                {
                    wherestatement += "SOCCER_SINGLE_PLAYER_INFO.ID= " + player.ID;
                }
            }
            if (player.Name != null &&
                player.Name != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_SINGLE_PLAYER_INFO.Name Like '%" + player.Name + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_SINGLE_PLAYER_INFO.Name Like '%" + player.Name + "%' ";
                }
            }
            if (player.ClubName != null &&
                player.ClubName != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_CLUB_INFO.Name Like '%" + player.ClubName + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_CLUB_INFO.Name Like '%" + player.ClubName + "%' ";
                }
            }
            if (player.NationalTeamName != null &&
                player.NationalTeamName != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_NATIONAL_TEAM_INFO.Name Like '%" + player.NationalTeamName + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_NATIONAL_TEAM_INFO.Name Like '%" + player.NationalTeamName + "%' ";
                }
            }
            return wherestatement;
        }
    }
}
